#-*- coding:UTF-8-*-

__author__='zhaoxp'


import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative

from sqlalchemy import Integer,String,Column

'''
sqlalchemy python ORM framework
this shows how to use sqlalchemy
'''

Base=sqlalchemy.ext.declarative.declarative_base()

class User(Base):
	__tablename__='users'
	
	id=Column(Integer,primary_key=True)
	name=Column(String(50))
	fullname=Column(String(50))
	password=Column(String(100))
	
	def __init__(self,name,fullname,password):
		self.name=name
		self.fullname=fullname
		self.password=password
	
	def __repr__(self):
		return "<User(name='%s',fullname='%s',password='%s')>"%(self.name,self.fullname,self.password)


db_config={'host':'10.99.201.86','user':'test','password':'test','db':'test','charset':'utf8'}
#pool_recycle=28800 this means to recycle connection periodically
engine=sqlalchemy.create_engine('mysql://%s:%s@%s/%s?charset=%s'%(db_config['user'],db_config['password'],db_config['host'],db_config['db'],db_config['charset']),pool_recycle=28800,echo=True)


def create_table():
	#连接数据库
	#db_config={'host':'10.99.201.86','user':'test','password':'test','db':'test','charset':'utf8'}
	#print('db_config = '+str(db_config))
	#engine=sqlalchemy.create_engine('mysql://%s:%s@%s/%s?charset=%s'%(db_config['user'],db_config['password'],db_config['host'],db_config['db'],db_config['charset']),echo=True)
	#创建表结构
	metadata=sqlalchemy.MetaData()
	users_table=sqlalchemy.Table('users',metadata,Column('id',Integer,primary_key=True),Column('name',String(50)),Column('fullname',String(50)),Column('password',String(100)))
	metadata.create_all(engine)
	return users_table

def creata_table2():
	metadata=sqlalchemy.MetaData(engine)
	users_table=sqlalchemy.Table('users',metadata,Column('id',Integer,primary_key=True),Column('name',String(50)),Column('fullname',String(50)),Column('password',String(100)))
	users_table.create()
	return users_table

def create_mapping():
	metadata=sqlalchemy.MetaData(engine)
	users_table=sqlalchemy.Table('users',metadata,autoload=True)
	print users_table.columns
	return users_table

def insert_usage(users_table):
	i=users_table.insert()
	i.execute(name='JFK',fullname='John Fitzgerald Kennedy',password='jfk001')
	i.execute(name='RF',fullname='Roger Federer',password='milk')

def insert_by_session():
	DBSession=sqlalchemy.orm.sessionmaker(bind=engine)
	session=DBSession()
	session.add(User('zhaoxp','zhaoxinpeng','zhaoxp123'))
	session.add(User('liuxt','liuxiaoteng','liuxt123'))
	session.commit()
	session.close()

def query_by_session():
	DBSession=sqlalchemy.orm.sessionmaker(bind=engine)
	session=DBSession()
	print(session.execute('show databases').fetchall())
	#user=session.query(User).filter(User.id=='4').one()
	#print('query user = '+str(user))
	session.close()

def query_by_session2():
	DBSession=sqlalchemy.orm.sessionmaker(bind=engine)
	session=DBSession()
	user=session.query(User).filter(User.id==3).one()
	print('query user = '+str(user))
	session.close()

if __name__=='__main__':
	print('sqlalchemy usage:');
	
	#print 'create_table()'
	#users_table=create_table()
	#users_table=create_table2()
	#users_table=create_mapping()
	#print('users_table = '+str(users_table))
	#insert_usage(users_table)
	#insert_by_session()
	#query_by_session()
	query_by_session2()
	
	